﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;

namespace NTQOnlineExam.Services.CommandService.Question
{
    public class ChangeQuestionOrderDbCommand : ICommand<bool>
    {
        private readonly int _questionId;
        private readonly int _newOrder;

        private const string SQL = @"
            Declare @OldOrder	INT	
	        DECLARE @SectionId INT
            DECLARE @TermId INT
	        SELECT	@OldOrder = [Order], @TermId = TermId, @SectionId = SectionId
	        FROM	dbo.Question
	        WHERE QuestionId = @QuestionId
	
	        IF(@OldOrder<=@NewOrder)
		        Set @NewOrder = @NewOrder + 1
	
	
	        UPDATE	f
	        SET		f.[Order] = @NewOrder
	        FROM	dbo.Question f 
	        WHERE QuestionId = @QuestionId
	
	        UPDATE	dbo.Question
	        SET		[Order] = [Order] + 1
	        WHERE	SectionId = @SectionId AND TermId = @TermId AND [Order] >= @NewOrder + 1
	
	        UPDATE	dbo.Question
	        SET		[Order] = [Order] - 1
	        WHERE	SectionId = @SectionId AND TermId = @TermId AND [Order] <= @NewOrder AND QuestionId <> @QuestionId 
	
	
	        UPDATE	f
	            SET		f.[Order] = c.ReOrder		
	            FROM	dbo.Question f INNER JOIN 
						            (SELECT QuestionId, row_number() over (ORDER BY [Order]) as ReOrder
						             FROM	dbo.Question
						            Where	SectionId = @SectionId AND TermId = @TermId ) AS c ON f.QuestionId = c.QuestionId
	            WHERE	f.TermId = @TermId AND f.SectionId = @SectionId
	
        ";
        public ChangeQuestionOrderDbCommand(int questionId, int newOrder)
        {
            _questionId = questionId;
            _newOrder = newOrder;
        }

        public bool Execute()
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];

            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(SQL, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@QuestionId", SqlDbType.Int).Value = _questionId;
                    cmd.Parameters.Add("@NewOrder", SqlDbType.Int).Value = _newOrder;
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return true;

        }
    }
}
